(async  function(){
    const koa = require('koa');
    const Router = require('koa-router')
    const StaticCache = require('koa-static-cache');
    const fs = require('fs');
    const bodyParser = require('koa-bodyparser');
    // 引入数据库模块
    const mysql = require('mysql2/promise')


    const app = new koa();
    const router = new Router();

    /**
     * 处理请求正文数据
     */
    app.use(bodyParser())

    // 使用静态资源
    app.use(
        StaticCache(__dirname + '/static',{
            prefix: '/static',  // 需要加的前缀
            gzip: true  // 是否开启gzip压缩
        })
    )
    app.use(
        StaticCache(__dirname + '/views',{
            prefix: '/views',  // 需要加的前缀
            gzip: true  // 是否开启gzip压缩
        })
    )


    // 使用连接数据库
    const connection = await mysql.createConnection({
        host: '127.0.0.1',
        user: 'root',
        password: '',
        database: 'test'
    });

    // 首页  因为readFileSync 这个是同步的，所以不需要 async
    router.get('/',ctx => {
        let content = fs.readFileSync('./views/index.html')
        ctx.body = content.toString();
    });

    // 查询
    router.get('/todos', async ctx => {
        // 执行sql语句
        // ORDER BY done DESC, id DESC
        console.log('ctx.request',ctx.request)

        // 当前页数
        let current_page = ctx.query.current_page;
        // 每页页数
        let page_size = ctx.query.page_size;
        let getAllTotalSQL = 'SELECT * FROM todos';
        let [allData] = await connection.query(getAllTotalSQL);
        // 总页数
        let totalPage = Math.ceil(allData.length / page_size);


        let result = await connection.query('SELECT * FROM todos LIMIT '+ page_size +' OFFSET '+ (current_page -1) *page_size);
        // 结构赋值
        let [list] = result;
        let datas = {
            code : 0,
            pageInfo:{
                totalPage,
                current_page:current_page*1,
                page_size:page_size*1
            },
            data : list
        }
        ctx.body = datas;
    });

    /**
     * 增加
     */
    router.post('/add',async (ctx, next) => {
        let title = ctx.request.body.title || '';

        if(!title){
            ctx.body = {
                code: 10001,
                data : null,
                msg: '添加的内容不能为空'
            }
        }else{

            let result = await connection.query('INSERT INTO todos (title,done) VALUES ("'+title+'",0)');
            let [rs] = result; // 解构赋值，获取第一项

            // 查找最后一项
            let [addItems_result] = await connection.query('SELECT * from todos ORDER BY id DESC LIMIT 1')
            let addItem = addItems_result[0];

            if(rs.affectedRows > 0){
                ctx.body = {
                    code : 0,
                    data: addItem,  // 返回添加成功的id
                    msg : '添加成功'
                }
            }else{
                ctx.body = {
                    code : 10002,
                    msg : '添加事变'
                }
            }


        }
    });


    /**
     * 删除
     */
    router.post('/remove',async (ctx, next) => {
        let id = ctx.request.body.id || 0;

        if(!id){
            ctx.body = {
                code: 10001,
                data : null,
                msg: '参数id错误'
            }
        }else{
            let [result] = await connection.query('DELETE FROM todos WHERE id='+id);

            if(result.affectedRows > 0){
                ctx.body = {
                    code : 0,
                    msg : '删除成功'
                }
            }else{
                ctx.body = {
                    code : 10002,
                    msg : '删除失败'
                }
            }
        }
    });


    /**
     *  改变状态
     */
    router.post('/change',async (ctx, next) => {
        let id = ctx.request.body.id || 0;
        let done = ctx.request.body.done;

        if(!done){
            done = 0;
        }else{
            done = 1;
        }

        console.log('/change id', id)

        if(!id){
            ctx.body = {
                code: 10001,
                data : null,
                msg: '参数id错误'
            }
        }else{

            let [result] = await connection.query('UPDATE todos SET done='+done+' where id='+id);
            console.log(result)

            if(result.affectedRows > 0){
                ctx.body = {
                    code : 0,
                    msg : '修改成功'
                }
            }else{
                ctx.body = {
                    code : 10002,
                    msg : '修改失败'
                }
            }
        }
    });



    app.use(router.routes());
    app.use(router.allowedMethods());

    app.listen('3000')
})()